package njust.edu.dao.impl;

import njust.edu.dao.DishDao;
import njust.edu.dbutils.SimpleUtils;
import njust.edu.entity.Dish;
import njust.edu.web.Page;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DishDaoImpl implements DishDao {

    @Override
    public Dish findById(String id) {
        String sql = "select id,name,type_id,price,picture,sale,stock,description from dish where id = ?";
        Dish dish = new Dish();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, id);
            rs = ps.executeQuery();
            while(rs.next()){
                dish  = new Dish(rs.getString(1)
                        ,rs.getString(2)
                        ,rs.getInt(3)
                        ,rs.getFloat(4)
                        ,rs.getString(5)
                        ,rs.getInt(6)
                        ,rs.getInt(7)
                        ,rs.getString(8));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dish;
    }

    @Override
    public Dish findByName(String name) {
        String sql = "select id,name,type_id,price,picture,sale,stock,description from dish where name = ?";
        Dish dish = new Dish();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            rs = ps.executeQuery();
            while(rs.next()){
                dish  = new Dish(rs.getString(1)
                        ,rs.getString(2)
                        ,rs.getInt(3)
                        ,rs.getFloat(4)
                        ,rs.getString(5)
                        ,rs.getInt(6)
                        ,rs.getInt(7)
                        ,rs.getString(8));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        SimpleUtils.release(conn,ps,rs);
        return dish;
    }

    @Override
    public void update(Dish dish) {
        String sql = "update dish set name=?,type_id=?,price=?,picture=?,sale=?,stock=?,description=? "
                + "where id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, dish.getName());
            ps.setInt(2, dish.getType_id());
            ps.setFloat(3, dish.getPrice());
            ps.setString(4, dish.getPicture());
            ps.setInt(5, dish.getSale());
            ps.setInt(6, dish.getStock());
            ps.setString(7, dish.getDescription());
            ps.setString(8, dish.getId());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        SimpleUtils.release(conn,ps,rs);
    }

    @Override
    public void delete(String id) {
        String sql = "delete from dish where id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        SimpleUtils.release(conn,ps,rs);
    }

    @Override
    public void add(Dish dish) {
        String sql = "insert into dish(id,name,type_id,price,picture,sale,stock,description) "
                + "values(?,?,?,?,?,?,?,?)";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, dish.getId());
            ps.setString(2, dish.getName());
            ps.setInt(3, dish.getType_id());
            ps.setFloat(4, dish.getPrice());
            ps.setString(5, dish.getPicture());
            ps.setInt(6, dish.getSale());
            ps.setInt(7, dish.getStock());
            ps.setString(8, dish.getDescription());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        SimpleUtils.release(conn,ps,rs);
    }

    @Override
    public List<Dish> getList() {
        String sql = "SELECT id,name,type_id,price,picture,sale,stock,description "
                + "FROM dish";
        List<Dish> lDish = new ArrayList<>();
        Dish dish = new Dish();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                dish  = new Dish(rs.getString(1)
                        ,rs.getString(2)
                        ,rs.getInt(3)
                        ,rs.getFloat(4)
                        ,rs.getString(5)
                        ,rs.getInt(6)
                        ,rs.getInt(7)
                        ,rs.getString(8));
                lDish.add(dish);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        SimpleUtils.release(conn,ps,rs);
        return lDish;
    }

    @Override
    public int getTotalDishNumber(String likeStr) {
        String sql = null;
        int count = -1;
        Connection conn =null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        if ("".equals(likeStr) || likeStr == null) {
            sql = "SELECT count(id) FROM dish";
        } else {
            sql = "SELECT count(id) "
                    + "FROM dish where name like '%"
                    + likeStr
                    + "%' or type_id like '%"
                    + likeStr+ "%'";
        }
        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if(rs.next()){
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        SimpleUtils.release(conn, ps, rs);
        return count;
    }

    @Override
    public List<Dish> getPageList(int n, int pageSize, String condition, String likeStr) {
        String sql = null;
        List<Dish> lDish = new ArrayList<>();
        Dish dish = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        if ("".equals(likeStr) || likeStr == null) {
            sql = "SELECT id,name,type_id,price,picture,sale,stock,description "
                    + "FROM dish order by " + condition + " LIMIT ?, ?";
        } else {
            sql = "SELECT id,name,type_id,price,picture,sale,stock,description "
                    + "FROM dish where name like '%"
                    + likeStr
                    + "%' order by "
                    + condition + " LIMIT ?, ?";
        }
        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, (n-1)*pageSize);
            ps.setInt(2, pageSize);
            rs = ps.executeQuery();
            while(rs.next()){
                dish  = new Dish(rs.getString(1)
                        ,rs.getString(2)
                        ,rs.getInt(3)
                        ,rs.getFloat(4)
                        ,rs.getString(5)
                        ,rs.getInt(6)
                        ,rs.getInt(7)
                        ,rs.getString(8));
                lDish.add(dish);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        SimpleUtils.release(conn,ps,rs);
        return lDish;
    }

    @Override
    public Page<Dish> getPage(int n, String condition, String likeStr) {

        Page<Dish> page = new Page<Dish>(n);
        page.setTotalItemNumber(getTotalDishNumber(likeStr));
        n = page.getPageNo();
        page.setList(getPageList(n, 10, condition, likeStr));

        return page;
    }

    @Override
    public List<Dish> getListByName(String keyword) {
        String sql = "SELECT id,name,type_id,price,picture,sale,stock,description "
                + "FROM dish where name like ?";
        List<Dish> lDish = new ArrayList<>();
        Dish dish = new Dish();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, keyword);
            rs = ps.executeQuery();
            while(rs.next()){
                dish  = new Dish(rs.getString(1)
                        ,rs.getString(2)
                        ,rs.getInt(3)
                        ,rs.getFloat(4)
                        ,rs.getString(5)
                        ,rs.getInt(6)
                        ,rs.getInt(7)
                        ,rs.getString(8));
                lDish.add(dish);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        SimpleUtils.release(conn,ps,rs);
        return lDish;
    }

}
